Visual Basic 6 Programming Blue Book: The Most Complete, Hands-On Resource for Writing Programs with Microsoft Visual Basic 6!
(Publisher: The Coriolis Group)
Author(s): Peter G. Aitken
ISBN: 1576102815
Publication Date: 08/01/98

In the Customers form, you again use the “chameleon Command Button” technique covered earlier in the book, permitting the four Command Buttons to do the work of six. When the user views or edits records in the default state, the global variable EnteringRecords is False and the first two Command Buttons have the captions “Add New” and “Delete Current”. When the user clicks on the Add New button, EnteringRecords is toggled to True, the Add New button’s caption is changed to Save, and the Delete Current button’s caption is changed to Cancel. When the user clicks on Save or Cancel after entering the new record, the process is reversed. While the user is entering a new record, you also want the List and Finished Command Buttons and the Data control to be disabled.

As for the database itself, the commands are fairly simple and consist of methods belonging to the RecordSet object. The RecordSet object is associated with the ADO Data control and is automatically created when the control connected to a data source and a query is run. Here are the methods you’ll use:

•  AddNew—Clears the bound controls in preparation for entry of new data

•  Update—After an AddNew, copies the data entered in the bound controls into a new record in the table

•  Delete—Deletes the current record from the table

•  CancelUpdate—After an AddNew, cancels the add record operation

•  Refresh—Updates the RecordSet to reflect any changes that have occurred

•  MoveNext—Moves to the next record

•  MoveLast—Moves to the last record

•  Requery—Repeats the active query and refreshes displayed data

As for the List All command, the capabilities of the DataGrid control make it simple to code. You only have to set the properties of the ADO Data control to which the DataGrid is bound so they point at the same database and table as the Customer form’s ADO Data control. Then, refresh the ADO Data control, and use the Show method to display the form. Here’s the code:

frmList.Adodc1.ConnectionString = Adodc1.ConnectionString
frmList.Adodc1.RecordSource = Adodc1.RecordSource
frmList.Caption = “Customers Table”
frmList.Adodc1.Refresh
frmList.Show

The full Command1_Click event procedure for the Customers form is shown in Listing 22.2.

Listing 22.2 The Command1_Click event procedure in CUSTOMER.FRM.

Private Sub Command1_Click(Index As Integer)

Dim Reply As Integer

Select Case Index
    Case 0      ‘ Add New or Save.
        If EnteringRecord Then      ‘ Save command.
            ‘ Save the new record.
            Adodc1.Recordset.Update
            EnteringRecord = False
            ‘ Change command buttons.
            Command1(0).Caption = “&Add New”
            Command1(1).Caption = “&Delete Current”
            ‘ Enable the data control.
            Adodc1.Enabled = True
            Adodc1.Recordset.Requery
            Adodc1.Recordset.MoveLast
            ‘ Enable List and Finished buttons.
            Command1(2).Enabled = True
            Command1(3).Enabled = True
        Else                        ‘ Add New command.
            EnteringRecord = True
           ‘ Put focus in first text box. This is actually the
           ‘ second TextBox as the first one (CustID) is locked.
            Text1(1).SetFocus
            ‘ Disable List and Finished buttons.
            Command1(2).Enabled = False
            Command1(3).Enabled = False
            ‘ Change command buttons.
            Command1(0).Caption = “&Save”
            Command1(1).Caption = “&Cancel”
            ‘ Disable the data control.
            Adodc1.Enabled = False
            ‘ Add a new record.
            Adodc1.Recordset.AddNew
        End If
    Case 1      ‘ Delete current or Cancel.
        If EnteringRecord Then      ‘ Cancel command.
            Reply = MsgBox(“Discard current entry?”, vbYesNo + vbQuestion, _                         “Cancel Entry”)
            If Reply = vbNo Then Exit Sub
            Adodc1.Recordset.CancelUpdate
            Adodc1.Refresh
            EnteringRecord = False
            ‘Change button captions.
            Command1(0).Caption = “&Add New”
            Command1(1).Caption = “&Delete Current”
             ‘ Enable List and Finished buttons.
            Command1(2).Enabled = True
            Command1(3).Enabled = True
            ‘ Enable ADO Data control.
            Adodc1.Enabled = True
        Else                        ‘ Delete Current command.
            Reply = MsgBox(“Delete this record?”, vbYesNo + _
                  vbQuestion, “Cancel Entry”)
            If Reply = vbNo Then Exit Sub
            Adodc1.Recordset.Delete
            Adodc1.Recordset.MoveNext
        End If
    Case 2      ‘ List all.
        frmList.Adodc1.ConnectionString = Adodc1.ConnectionString
        frmList.Adodc1.RecordSource = Adodc1.RecordSource
        frmList.Caption = “Customers Table”
        frmList.Adodc1.Refresh
        frmList.Show
    Case 3      ‘ Finished.
        Hide
End Select

End Sub